﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using SystemDirectory.Common;
using SystemDirectory.Entity;

namespace SystemDirectory.DAO
{
    public class DAOProgramme
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString2"].ToString());
        SqlCommand com;
        SqlDataAdapter adapt;
        DataTable dtResult;
        public DataTable Get(bool ShowInactive, Utilities.SearchType IsSearch)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand com = new SqlCommand("SELECT * FROM Programme", con);

            switch (IsSearch)
            {
                case Utilities.SearchType.TypeAll:
                    {
                        if (ShowInactive)
                            com = new SqlCommand("SELECT * FROM Programme", con);
                        else
                            com = new SqlCommand("SELECT * FROM Programme WHERE IsActive = 'true'", con);
                        break;
                    }

                case Utilities.SearchType.Type09:
                    {
                        if (ShowInactive)
                        {
                            com = new SqlCommand("SELECT * FROM Programme WHERE ProgrammeName LIKE '0%' OR ProgrammeName LIKE '1%' OR ProgrammeName LIKE '2%' OR ProgrammeName LIKE '3%' OR ProgrammeName LIKE '4%'OR ProgrammeName LIKE '5%' OR ProgrammeName LIKE '6%' OR ProgrammeName LIKE '7%' OR ProgrammeName LIKE '9%'", con);
                        }
                        else
                        {
                            com = new SqlCommand("SELECT * FROM Programme WHERE (ProgrammeName LIKE '0%' OR ProgrammeName LIKE '1%' OR ProgrammeName LIKE '2%' OR ProgrammeName LIKE '3%' OR ProgrammeName LIKE '4%'OR ProgrammeName LIKE '5%' OR ProgrammeName LIKE '6%' OR ProgrammeName LIKE '7%' OR ProgrammeName LIKE '9%') AND IsActive = 'true'", con);
                        }
                        break;
                    }

                case Utilities.SearchType.TypeAE:
                    {
                        if (ShowInactive)
                        {
                            com = new SqlCommand("SELECT * FROM Programme WHERE ProgrammeName LIKE 'A%' OR ProgrammeName LIKE 'B%' OR ProgrammeName LIKE 'C%' OR ProgrammeName LIKE 'D%' OR ProgrammeName LIKE 'E%'", con);
                        }
                        else
                        {
                            com = new SqlCommand("SELECT * FROM Programme WHERE ( ProgrammeName LIKE 'A%' OR ProgrammeName LIKE 'B%' OR ProgrammeName LIKE 'C%' OR ProgrammeName LIKE 'D%' OR ProgrammeName LIKE 'E%') AND IsActive = 'true'", con);
                        }
                        break;
                    }
                case Utilities.SearchType.TypeFJ:
                    {
                        if (ShowInactive)
                        {
                            com = new SqlCommand("SELECT * FROM Programme WHERE ProgrammeName LIKE 'F%' OR ProgrammeName LIKE 'G%' OR ProgrammeName LIKE 'H%' OR ProgrammeName LIKE 'I%' OR ProgrammeName LIKE 'J%'", con);
                        }
                        else
                        {
                            com = new SqlCommand("SELECT * FROM Programme WHERE ( ProgrammeName LIKE 'F%' OR ProgrammeName LIKE 'G%' OR ProgrammeName LIKE 'H%' OR ProgrammeName LIKE 'I%' OR ProgrammeName LIKE 'J%') AND IsActive = 'true'", con);
                        }
                        break;
                    }
                case Utilities.SearchType.TypeKM:
                    if (ShowInactive)
                    {
                        com = new SqlCommand("SELECT * FROM Programme WHERE ProgrammeName LIKE 'K%' OR ProgrammeName LIKE 'L%' OR ProgrammeName LIKE 'M%' OR ProgrammeName LIKE 'N%'", con);
                    }
                    else
                    {
                        com = new SqlCommand("SELECT * FROM Programme WHERE (ProgrammeName LIKE 'K%' OR ProgrammeName LIKE 'L%' OR ProgrammeName LIKE 'M%' OR ProgrammeName LIKE 'N%') AND IsActive = 'true'", con);
                    }
                    break;
                case Utilities.SearchType.TypeOR:
                    if (ShowInactive)
                    {
                        com = new SqlCommand("SELECT * FROM Programme WHERE ProgrammeName LIKE 'O%' OR ProgrammeName LIKE 'P%' OR ProgrammeName LIKE 'Q%' OR ProgrammeName LIKE 'R%'", con);
                    }
                    else
                    {
                        com = new SqlCommand("SELECT * FROM Programme WHERE ( ProgrammeName LIKE 'O%' OR ProgrammeName LIKE 'P%' OR ProgrammeName LIKE 'Q%' OR ProgrammeName LIKE 'R%') AND IsActive = 'true'", con);
                    }
                    break;
                case Utilities.SearchType.TypeSV:
                    if (ShowInactive)
                    {
                        com = new SqlCommand("SELECT * FROM Programme WHERE ProgrammeName LIKE 'S%' OR ProgrammeName LIKE 'T%' OR ProgrammeName LIKE 'U%' OR ProgrammeName LIKE 'V%'", con);
                    }
                    else
                    {
                        com = new SqlCommand("SELECT * FROM Programme WHERE ( ProgrammeName LIKE 'S%' OR ProgrammeName LIKE 'T%' OR ProgrammeName LIKE 'U%' OR ProgrammeName LIKE 'V%') AND IsActive = 'true'", con);
                    }
                    break;
                case Utilities.SearchType.TypeWZ:
                    if (ShowInactive)
                    {
                        com = new SqlCommand("SELECT * FROM Programme WHERE ProgrammeName LIKE 'W%' OR ProgrammeName LIKE 'X%' OR ProgrammeName LIKE 'Y%' OR ProgrammeName LIKE 'Z%'", con);
                    }
                    else
                    {
                        com = new SqlCommand("SELECT * FROM Programme WHERE ( ProgrammeName LIKE 'W%' OR ProgrammeName LIKE 'X%' OR ProgrammeName LIKE 'Y%' OR ProgrammeName LIKE 'Z%') AND IsActive = 'true'", con);
                    }
                    break;

            }
            SqlDataAdapter adapt = new SqlDataAdapter(com);
            DataTable dtResult = new DataTable();
            adapt.Fill(dtResult);
            return dtResult;
        }

        public Programme GetByID(int id)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand com;
            com = new SqlCommand("SELECT * FROM Programme WHERE ProgrammeID=@id", con);
            com.Parameters.Add("@id", SqlDbType.Int);
            com.Parameters["@id"].Value = id;
            SqlDataAdapter adapt = new SqlDataAdapter(com);
            DataTable dtResult = new DataTable();
            adapt.Fill(dtResult);

            Programme pg = new Programme();
            pg.ProgrammeID = id;
            pg.ProgrammeName = dtResult.Rows[0]["ProgrammeName"].ToString();
            pg.IsActive = Convert.ToBoolean(dtResult.Rows[0]["IsActive"].ToString());
            pg.Description = dtResult.Rows[0]["Description"].ToString();
            return pg;
        }





        
        public bool UpdateProgramme(Programme pg)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand com = new SqlCommand("UPDATE Programme SET ProgrammeName=@ProgrammeName,Description=@Description WHERE ProgrammeID=@id", con);
            com.Parameters.Add("@id", SqlDbType.Int);
            com.Parameters["@id"].Value = Convert.ToInt32(pg.ProgrammeID);

            com.Parameters.Add("@ProgrammeName", SqlDbType.NVarChar, 255);
            com.Parameters["@ProgrammeName"].Value = pg.ProgrammeName;

            com.Parameters.Add("@Description", SqlDbType.NVarChar, 4000);
            com.Parameters["@Description"].Value = pg.Description;

            return Convert.ToBoolean(com.ExecuteNonQuery());
        }

        public bool AddProgramme(Programme pg)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand com = new SqlCommand(@"INSERT INTO Programme (ContactID,ProgrammeName,Description,IsActive)VALUES(@ContactId,@ProgrammeName,@Description,@IsActive)", con);
            com.Parameters.Add("@ContactId", SqlDbType.Int);
            com.Parameters["@ContactId"].Value = pg.ContactID;

            com.Parameters.Add("@ProgrammeName", SqlDbType.NVarChar, 255);
            com.Parameters["@ProgrammeName"].Value = pg.ProgrammeName;

            com.Parameters.Add("@Description", SqlDbType.NVarChar, 4000);
            com.Parameters["@Description"].Value = pg.Description;

            com.Parameters.Add("@IsActive", SqlDbType.Bit);
            com.Parameters["@IsActive"].Value = pg.IsActive;

            return Convert.ToBoolean(com.ExecuteNonQuery());
        }
    }
}